package com.bj58.data.emailreports.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.bj58.data.emailreports.dao.SrcDbPool.DBType;
import com.bj58.data.emailreports.model.NetflowWRData;
import com.bj58.data.emailreports.service.NetflowWeekReportService;
import com.bj58.data.emailreports.utils.CommonDataDeal;
import com.bj58.data.emailreports.utils.DateUtil;

public class NetflowWeekReportServiceImpl implements NetflowWeekReportService {
	
	private CommonDataDeal comDeal = new CommonDataDeal();
	private static Map<String,String> sourceMap_58pc = new HashMap<String, String>();
	static{
		sourceMap_58pc.put("all", "source1 in ('all')");
		sourceMap_58pc.put("ziyou", "source1 in ('直接访问','站内引荐')");
		sourceMap_58pc.put("guanggaoxilie", "source1 in ('广告系列')");
		sourceMap_58pc.put("lianmeng", "source1 in ('联盟')");
		sourceMap_58pc.put("sem", "source1 in ('SEM')");
		sourceMap_58pc.put("seo", "source1 in ('SEO')");
		sourceMap_58pc.put("qita", "source1 in ('EDM','58系PC站点','站外引荐','其他')");		
	}
	
	private static Map<String,String> sourceMap_58m = new HashMap<String, String>();
	static{
		sourceMap_58m.put("all", "source1 ='all' and source2='all'");
		sourceMap_58m.put("ziyou", "source1 = 'ziyou' and source2='all'");
		sourceMap_58m.put("sem", "source1 = 'qd' and source2 = 'sem' and source3='all'");
		sourceMap_58m.put("seo", "source1 ='sou' and source2='all'");
		sourceMap_58m.put("qita",  "source1 in ('qd','other') and source2 in ('other','qqclient','58系APP站点','58系M站点')");		
		sourceMap_58m.put("fufeiliulanqi", "source1 = 'qd' and source2 = 'fufeiliulanqi' and source3='all'");
		sourceMap_58m.put("fufeiqita", "source1 = 'qd' and source2 = 'fufeiqita' and source3='all'");
		sourceMap_58m.put("shoujiqq", "source1 = 'shoujiqq' and source2='all'");
		sourceMap_58m.put("momo", "source1 = 'momo' and source2='all'");
		sourceMap_58m.put("weixilie",  "source2 like '%wei%'");
		sourceMap_58m.put("lianmeng", "source1 = 'qd' and source2 = 'lianmeng' and source3='all'");	
	}
	
	private static Map<String,String> sourceMap_ganji = new HashMap<String, String>();
	static{
		sourceMap_ganji.put("all", "source1 = 'all'");
		sourceMap_ganji.put("ziyou", "source1 = 'self'");
		sourceMap_ganji.put("fufei", "source1 = 'tg'");
		sourceMap_ganji.put("sem", "source1 = 'sem'");
		sourceMap_ganji.put("seo", "source1 = 'seo'");
		sourceMap_ganji.put("qita", "source1 = 'other'");		
	}
//	private static Map<String,String> sourceMap_58pc = new HashMap<String,String>(){{
//		put("all", "source1 in ('all')");
//		put("ziyou", "source1 in ('直接访问','站内引荐')");
//		put("guanggaoxilie", "source1 in ('广告系列')");
//		put("lianmeng", "source1 in ('联盟')");
//		put("sem", "source1 in ('SEM')");
//		put("seo", "source1 in ('SEO')");
//		put("qita", "source1 in ('其他')");
//	}};
//			
//	private static Map<String,String> sourceMap_58m = new HashMap<String,String>(){{
//		put("all", "source1 ='all' and source2='all'");
//		put("ziyou", "source1 = 'ziyou' and source2='all'");
//		put("sem", "source1 = 'qd' and source2 = 'sem' and source3='all'");
//		put("seo", "source1 ='sou' and source2='all'");
//		put("qita",  "source1 ='other' and source2='all'");		
//		put("fufeiliulanqi", "source1 = 'qd' and source2 = 'fufeiliulanqi' and source3='all'");
//		put("fufeiqita", "source1 = 'qd' and source2 = 'fufeiqita' and source3='all'");
//		put("shoujiqq", "source1 = 'shoujiqq' and source2='all'");
//		put("momo", "source1 = 'momo' and source2='all'");
//		put("weixilie",  "source2 like '%wei%'");
//		put("lianmeng", "source1 = 'qd' and source2 = 'lianmeng' and source3='all'");
//	}};
//			
//	private static Map<String,String> sourceMap_ganji = new HashMap<String,String>(){{
//		put("all", "source1 = 'all'");
//		put("ziyou", "source1 = 'self'");
//		put("fufei", "source1 = 'tg'");
//		put("sem", "source1 = 'sem'");
//		put("seo", "source1 = 'seo'");
//		put("qita", "source1 = 'other'");
//	}};
	
	/**检查是否跨月
	 * @param endDate
	 * @return
	 */
	private List<String>  checkTwoMonth(String endDate){
		String beginDate = DateUtil.getStringDate(DateUtil.getDateBefore(DateUtil.strToDate(endDate, "yyyy-MM-dd"), 6), "yyyy-MM-dd") ;
		List<String> monthList =  DateUtil.subSearchDate(beginDate, endDate);
		return monthList;
	}
	
	
	@Override
	public Map<String,List<NetflowWRData>> getNWRBusiDataMap(List<String> dateList,String platformType,String[] business_58,String[] business_ganji)
			throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();			
		
		String[] companyArray={"58","ganji"};
		String[] businessArray={};
		
		for(String searchDate : dateList){
					
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);		
			
			//58 ganji逻辑一样			
			for(String company:companyArray)
			{			
				//确定业务线种类
				if(company.equals("58"))
				{
					businessArray=business_58;
				}else{
					businessArray=business_ganji;
				}
				
				for(String businessType : businessArray){
				
					sql.delete(0, sql.length()+1);					
					sql.append(" select '" + searchDate + "','"+company+"_"+businessType+"',");
					sql.append(" round(sum(a.vppv)/ count(distinct a.stat_date),0)  as'vppv', ");
					sql.append(" round(sum(a.uv)/ count(distinct a.stat_date),0)  as'uv',");
					sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),0)  as'pv', ");
					sql.append(" round(sum(a.vppv)/ sum(a.uv),2)  as'vppv/uv' ");
					sql.append(" from( ");
					
					sql.append(" select stat_date,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ," +
							"sum(case when page_type='all' then pv else 0  end) pv  ");			
					
					String table="";
					if(company.equals("ganji"))
					{
						table="ganji_";
					}
			
					if("app".equals(platformType)){
						
						sql.append(" from t_netflow_"+table+platformType+"_pv_"  + monthList.get(0).split(";")[0]);
					}
					else{
						sql.append(" from t_netflow_"+table+platformType+"_source_cate_pv_" + monthList.get(0).split(";")[0]);
					}	
					
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
					
					sql.append(" and business_type = '"+businessType+"'  and disp_cate1_name='all'");
					
					if("app".equals(platformType)){
						sql.append(" and  disp_city1_name='all' ");
					}
					else{
						sql.append(" and source1='all'");
					}		
					sql.append(" group by stat_date ");
					
					//跨月处理
					if(monthList.size() > 1){
						
						sql.append(" UNION all  ");
						sql.append(" select stat_date,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ," +
						"sum(case when page_type='all' then pv else 0  end) pv  ");			

						if("app".equals(platformType)){
							
							sql.append(" from t_netflow_"+table+platformType+"_pv_"  + monthList.get(1).split(";")[0]);
						}
						else{
							sql.append(" from t_netflow_"+table+platformType+"_source_cate_pv_" + monthList.get(1).split(";")[0]);
						}	
						
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
						
						sql.append(" and business_type = '"+businessType+"'  and disp_cate1_name='all'");
						
						if("app".equals(platformType)){
							sql.append(" and  disp_city1_name='all' ");
						}
						else{
							sql.append(" and source1='all'");
						}		
						sql.append(" group by stat_date ");
					}

					sql.append(" )a ");	
					if(company.equals("ganji"))
					{
						sql.append(" :"+ DBType.db_131_data_bi.getValue());
					}else{

						sql.append(" :"+ DBType.db_94_data_app.getValue());
					}											
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("type");
					cols1.add("vppv");
					cols1.add("uv");
					cols1.add("pv");
					cols1.add("vppv_uv");
	
					sqlMap.put(sql.toString(), cols1);
				}
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<NetflowWRData> rsList = comDeal.AssembledDataBySqlMap(sqlMap, NetflowWRData.class, gbList);
		
		//把all相关放入总体map
		Map<String,NetflowWRData> allCountMap = new HashMap<String, NetflowWRData>();
		
		for (NetflowWRData vo : rsList)
		{
			if(vo.getType().contains("all"))
			{
				allCountMap.put(vo.getStatDate()+vo.getType(), vo);	
			}
		}
	
		//计算占比
		for (NetflowWRData vo : rsList){
			
			if(vo.getType().contains("all"))
				continue;
		
			String key = vo.getStatDate()+vo.getType();
			key=key.substring(0,key.indexOf("_"))+"_all";
			if(allCountMap.containsKey(key)){
				NetflowWRData allVo = allCountMap.get(key);
				vo.setUvZb((double) vo.getUv() / allVo.getUv());
				vo.setPvZb((double) vo.getPv() / allVo.getPv());
				vo.setVppvZb((double) vo.getVppv() / allVo.getVppv());
			}
		}
		
		Map<String,List<NetflowWRData>>  rsMap = new HashMap<String, List<NetflowWRData>>();
		for (NetflowWRData vo : rsList) {
			
			String type = vo.getType().substring(0,vo.getType().indexOf("_"));
			
			List<NetflowWRData> tempList = new ArrayList<NetflowWRData>();
			if(rsMap.containsKey(type)){
				tempList = rsMap.get(type);
			}		
			tempList.add(vo);
			rsMap.put(type, tempList);
		}
		
		return rsMap;
	}
	
	@Override
	public Map<String,List<NetflowWRData>> getNWRplatformDataMap(List<String> dateList,String business)
			throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		List<String> platformTypeList = new ArrayList<String>();
		platformTypeList.add("pc");
		platformTypeList.add("m");
		platformTypeList.add("app");
		
		String[] companyArray={"58","ganji"};
		
		for(String searchDate : dateList){
			
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);		
			
			//58 ganji逻辑一样			
			for(String company:companyArray)
			{
				if(company.equals("ganji") && business.equals("ershouchuangxin"))
					continue;
				
				for(String platformType : platformTypeList){
					
					sql.delete(0, sql.length()+1);					
					sql.append(" select '" + searchDate + "', '"+company+"_"+platformType+"',");
					sql.append(" round(sum(a.vppv)/ count(distinct a.stat_date),0)  as'vppv', ");
					sql.append(" round(sum(a.uv)/ count(distinct a.stat_date),0)  as'uv',");
					sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),0)  as'pv', ");
					sql.append(" round(sum(a.vppv)/ sum(a.uv),2)  as'vppv/uv' ");
					sql.append(" from( ");
					
					sql.append(" select stat_date,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ," +
							"sum(case when page_type='all' then pv else 0  end) pv  ");			
					
					String table="";
					if(company.equals("ganji"))
					{
						table="ganji_";
					}
					
					if("app".equals(platformType)){
						
						sql.append(" from t_netflow_"+table+platformType+"_pv_"  + monthList.get(0).split(";")[0]);
					}
					else{
						sql.append(" from t_netflow_"+table+platformType+"_source_cate_pv_" + monthList.get(0).split(";")[0]);
					}	
					
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");
						
					sql.append(" and business_type = '"+business+"'  and disp_cate1_name='all'");
					if("app".equals(platformType)){
						sql.append(" and  disp_city1_name='all' ");
					}
					else{
						sql.append(" and source1='all'");
					}
					sql.append(" group by stat_date ");	
					
					//跨月处理
					if(monthList.size() > 1){
						
						sql.append(" UNION all  ");
						sql.append(" select stat_date,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ," +
						"sum(case when page_type='all' then pv else 0  end) pv  ");								
						
						if("app".equals(platformType)){
							
							sql.append(" from t_netflow_"+table+platformType+"_pv_"  + monthList.get(0).split(";")[0]);
						}
						else{
							sql.append(" from t_netflow_"+table+platformType+"_source_cate_pv_" + monthList.get(0).split(";")[0]);
						}	
						
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");
							
						sql.append(" and business_type = '"+business+"'  and disp_cate1_name='all'");
						if("app".equals(platformType)){
							sql.append(" and  disp_city1_name='all' ");
						}
						else{
							sql.append(" and source1='all'");
						}
						sql.append(" group by stat_date ");					
					}					
					sql.append(" )a ");	
					
					if(company.equals("ganji"))
					{
						sql.append(" :"+ DBType.db_131_data_bi.getValue());
					}else{

						sql.append(" :"+ DBType.db_94_data_app.getValue());
					}										
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("type");
					cols1.add("vppv");
					cols1.add("uv");
					cols1.add("pv");
					cols1.add("vppv_uv");

					sqlMap.put(sql.toString(), cols1);
				}
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<NetflowWRData> rsList = comDeal.AssembledDataBySqlMap(sqlMap, NetflowWRData.class, gbList);
		
		//计算三端总体
		Map<String,NetflowWRData> allCountMap = new HashMap<String, NetflowWRData>();	
		
		for(String company:companyArray){
			
			if(company.equals("ganji") && business.equals("ershouchuangxin"))
				continue;
	
			for(String date:dateList)
			{			
				NetflowWRData allvo = new NetflowWRData();
				allvo.setStatDate(date);
				allvo.setType(company+"_all");
				for (NetflowWRData vo : rsList)
				{
					if(vo.getType().contains(company) && vo.getStatDate().equals(date))
					{
						allvo.setVppv(vo.getVppv()+allvo.getVppv());
						allvo.setUv(vo.getUv()+allvo.getUv());
						allvo.setPv(vo.getPv()+allvo.getPv());			
					}			
				}
				allvo.setVppv_uv((double)allvo.getVppv()/allvo.getUv());					
				allCountMap.put(allvo.getStatDate()+allvo.getType(), allvo);
				rsList.add(allvo);			
			}
		}
		
		//计算占比
		for (NetflowWRData vo : rsList){
		
			if(vo.getType().contains("all"))
				continue;
			
			String key = vo.getStatDate()+vo.getType();
			key=key.substring(0,key.indexOf("_"))+"_all";
			if(allCountMap.containsKey(key)){
				NetflowWRData allVo = allCountMap.get(key);
				vo.setUvZb((double) vo.getUv() / allVo.getUv());
				vo.setPvZb((double) vo.getPv() / allVo.getPv());
				vo.setVppvZb((double) vo.getVppv() / allVo.getVppv());
			}
		}
		
		Map<String,List<NetflowWRData>>  rsMap = new HashMap<String, List<NetflowWRData>>();
		for (NetflowWRData vo : rsList) {
			
			String type = vo.getType().substring(0,vo.getType().indexOf("_"));
			
			List<NetflowWRData> tempList = new ArrayList<NetflowWRData>();
			if(rsMap.containsKey(type)){
				tempList = rsMap.get(type);
			}		
			tempList.add(vo);
			rsMap.put(type, tempList);
		}		
		return rsMap;
	}
	
	@Override
	public Map<String,List<NetflowWRData>> getNWRSourceDataMap(List<String> dateList,String platform,String business)
			throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		Map<String,String> sourceMap=new HashMap<String,String>();			
		String[] companyArray={"58","ganji"};
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);			
			
			//58 和ganji一起
			for(String company : companyArray){
				
				if(company.equals("ganji") && business.equals("ershouchuangxin"))
					continue;
				
				//确定来源map
				if(platform.equals("pc") && company.equals("58"))
				{
					sourceMap=sourceMap_58pc;
				}else if(platform.equals("m") && company.equals("58"))
				{
					sourceMap=sourceMap_58m;
				}else{
					sourceMap=sourceMap_ganji;
				}
	
				for(String source : sourceMap.keySet()){
				
					sql.delete(0, sql.length()+1);					
					sql.append(" select '" + searchDate + "', '"+company+"_"+source+"',");
					sql.append(" round(sum(a.vppv)/ count(distinct a.stat_date),0)  as'vppv', ");
					sql.append(" round(sum(a.uv)/ count(distinct a.stat_date),0)  as'uv',");
					sql.append(" round(sum(a.vppv)/ sum(a.uv),2)  as'vppv/uv' ");
					sql.append(" from( ");
					
					sql.append(" select stat_date,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ");			
								
					String table="";
					if(company.equals("ganji"))
					{
						table="ganji_";
					}										
					sql.append(" from t_netflow_"+table+platform+"_source_cate_pv_" + monthList.get(0).split(";")[0]);																	
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");					
					sql.append(" and business_type  = '"+business+"'");
					sql.append(" and disp_cate1_name='all' ");
					
					if(platform.equals("pc"))
					{
						sql.append(" and source2='all' ");
					}					
					sql.append(" and "+sourceMap.get(source));										
					sql.append(" group by stat_date ");
					
					//跨月处理
					if(monthList.size() > 1){
						sql.append(" UNION all  ");
						sql.append(" select stat_date,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ");			
						
						sql.append(" from t_netflow_"+table+platform+"_source_cate_pv_" + monthList.get(1).split(";")[0]);													
						sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
						sql.append(" and stat_date <= '"+searchDate+ "' ");					
						sql.append(" and business_type  = '"+business+"'");
						sql.append(" and disp_cate1_name='all' ");
						
						if(platform.equals("pc"))
						{
							sql.append(" and source2='all' ");
						}						
						sql.append(" and "+sourceMap.get(source));							
						sql.append(" group by stat_date ");
					}
						
					sql.append(" )a ");	
					
					if(company.equals("ganji"))
					{
						sql.append(" :"+ DBType.db_131_data_bi.getValue());
					}else{

						sql.append(" :"+ DBType.db_94_data_app.getValue());
					}
											
					List<String> cols1 = new ArrayList<String>();
					cols1.add("statDate");
					cols1.add("type");
					cols1.add("vppv");
					cols1.add("uv");
					cols1.add("vppv_uv");
	
					sqlMap.put(sql.toString(), cols1);
				}			
			}
		}
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<NetflowWRData> rsList = comDeal.AssembledDataBySqlMap(sqlMap, NetflowWRData.class, gbList);
		
		//把总计放入map
		Map<String,NetflowWRData> allCountMap = new HashMap<String, NetflowWRData>();
		for (NetflowWRData vo : rsList)
		{
			if(vo.getType().contains("all"))
			{
				allCountMap.put(vo.getStatDate()+vo.getType(), vo);
			}
		}

		//计算占比
		for (NetflowWRData vo : rsList){
			
			if(vo.getType().contains("all"))
				continue;
		
			String key = vo.getStatDate()+vo.getType();
			key=key.substring(0,key.indexOf("_"))+"_all";
			if(allCountMap.containsKey(key)){
				NetflowWRData allVo = allCountMap.get(key);
				vo.setUvZb((double) vo.getUv() / allVo.getUv());
				vo.setVppvZb((double) vo.getVppv() / allVo.getVppv());
			}
		}
		
		Map<String,List<NetflowWRData>>  rsMap = new HashMap<String, List<NetflowWRData>>();
		for (NetflowWRData vo : rsList) {
			
			String type = vo.getType().substring(0,vo.getType().indexOf("_"));
			
			List<NetflowWRData> tempList = new ArrayList<NetflowWRData>();
			if(rsMap.containsKey(type)){
				tempList = rsMap.get(type);
			}		
			tempList.add(vo);
			rsMap.put(type, tempList);
		}	
		return rsMap;
	}

	@Override
	public Map<String,List<NetflowWRData>> getNWRCate1DataMap(List<String> dateList,String platform,String business)
			throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
				
		String[] companyArray={"58","ganji"};
		
		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);			
			
			//58 和ganji一起
			for(String company : companyArray){
				
				if(company.equals("ganji") && business.equals("ershouchuangxin"))
					continue;
					
				sql.delete(0, sql.length()+1);					
				sql.append(" select '" + searchDate + "', CONCAT('"+company+"','_',a.disp_cate1_name),");
				sql.append(" round(sum(a.vppv)/ count(distinct a.stat_date),0)  as'vppv', ");
				sql.append(" round(sum(a.uv)/ count(distinct a.stat_date),0)  as'uv',");
				sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),0)  as'pv', ");
				sql.append(" round(sum(a.vppv)/ sum(a.uv),2)  as'vppv/uv' ");
				sql.append(" from( ");
					
				sql.append(" select stat_date,disp_cate1_name,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ," +
						"sum(case when page_type='all' then pv else 0  end) pv  ");			
					
				String table="";
				if(company.equals("ganji"))
				{
					table="ganji_";
				}
					
				if("app".equals(platform)){
						
					sql.append(" from t_netflow_"+table+platform+"_pv_"  + monthList.get(0).split(";")[0]);
				}
				else{
					sql.append(" from t_netflow_"+table+platform+"_source_cate_pv_" + monthList.get(0).split(";")[0]);
				}	
					
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");						
				sql.append(" and business_type = '"+business+"' ");
				
				if(business.equals("ershouchuangxin"))
				{
					sql.append(" and disp_cate1_name in ('宠物','二手交易','回收')");							
				}	
			
				if("app".equals(platform)){
					sql.append(" and  disp_city1_name='all' ");
				}
				else{
					sql.append(" and source1='all'");
				}
				sql.append(" and disp_cate2_name='all' ");
				sql.append(" group by stat_date,disp_cate1_name ");	
					
				//跨月处理
				if(monthList.size() > 1){
						
					sql.append(" UNION all  ");					
					sql.append(" select stat_date,disp_cate1_name,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ," +
							"sum(case when page_type='all' then pv else 0  end) pv  ");			
																			
					if("app".equals(platform)){
								
						sql.append(" from t_netflow_"+table+platform+"_pv_"  + monthList.get(1).split(";")[0]);
					}
					else{
						sql.append(" from t_netflow_"+table+platform+"_source_cate_pv_" + monthList.get(1).split(";")[0]);
					}	
							
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");							
					sql.append(" and business_type = '"+business+"' ");
					
					if(business.equals("ershouchuangxin"))
					{
						sql.append(" and disp_cate1_name in ('宠物','二手交易','回收')");							
					}	
					
					if("app".equals(platform)){
						sql.append(" and  disp_city1_name='all' ");
					}
					else{
						sql.append(" and source1='all'");
					}
					sql.append(" and disp_cate2_name='all' ");
					sql.append(" group by stat_date,disp_cate1_name ");					
				}
					
				sql.append(" )a ");	
				sql.append(" group by disp_cate1_name ");	
					
				if(company.equals("ganji"))
				{
					sql.append(" :"+ DBType.db_131_data_bi.getValue());
				}else{

					sql.append(" :"+ DBType.db_94_data_app.getValue());
				}
											
				List<String> cols1 = new ArrayList<String>();
				cols1.add("statDate");
				cols1.add("type");
				cols1.add("vppv");
				cols1.add("uv");
				cols1.add("pv");
				cols1.add("vppv_uv");

				sqlMap.put(sql.toString(), cols1);
			}
							
		}	
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<NetflowWRData> rsList = comDeal.AssembledDataBySqlMap(sqlMap, NetflowWRData.class, gbList);
	
		Map<String,List<NetflowWRData>>  rsMap = new HashMap<String, List<NetflowWRData>>();
		for (NetflowWRData vo : rsList) {
			
			String type = vo.getType().substring(0,vo.getType().indexOf("_"));
			
			List<NetflowWRData> tempList = new ArrayList<NetflowWRData>();
			if(rsMap.containsKey(type)){
				tempList = rsMap.get(type);
			}		
			tempList.add(vo);
			rsMap.put(type, tempList);
		}		
		return rsMap;
	}

	
	@Override
	public Map<String,List<NetflowWRData>> getNWRADDMap(List<String> dateList,Map<String,List<NetflowWRData>> PCDataMap, Map<String,List<NetflowWRData>> MDataMap,Map<String,List<NetflowWRData>> APPDataMap,String business)
			throws Exception {

		String[] companyArray={"58","ganji"};
		
		List<NetflowWRData> rsList=new ArrayList<NetflowWRData>();

		for(String company: companyArray){
			
			if(company.equals("ganji") && business.equals("ershouchuangxin"))
				continue;

			List<NetflowWRData> pcData=PCDataMap.get(company);
			List<NetflowWRData> mData=MDataMap.get(company);
			List<NetflowWRData> appData=APPDataMap.get(company);
			
			//以pc为基准 来匹配m和app的
			for (NetflowWRData pcvo : pcData)
			{
				NetflowWRData allvo = new NetflowWRData();
				allvo.setStatDate(pcvo.getStatDate());
				allvo.setType(pcvo.getType());
				
				allvo.setVppv(pcvo.getVppv()+allvo.getVppv());
				allvo.setUv(pcvo.getUv()+allvo.getUv());		
				allvo.setPv(pcvo.getPv()+allvo.getPv());	
						
				for (NetflowWRData mvo : mData)
				{
					if(mvo.getType().equals(pcvo.getType()) && mvo.getStatDate().equals(pcvo.getStatDate()))
					{
						allvo.setVppv(mvo.getVppv()+allvo.getVppv());
						allvo.setUv(mvo.getUv()+allvo.getUv());		
						allvo.setPv(mvo.getPv()+allvo.getPv());	
						break;
					}			
				}
				
				for (NetflowWRData appvo : appData)
				{
					if(appvo.getType().equals(pcvo.getType()) && appvo.getStatDate().equals(pcvo.getStatDate()))
					{
						allvo.setVppv(appvo.getVppv()+allvo.getVppv());
						allvo.setUv(appvo.getUv()+allvo.getUv());		
						allvo.setPv(appvo.getPv()+allvo.getPv());	
						break;
					}			
				}

				allvo.setVppv_uv((double)allvo.getVppv()/allvo.getUv());				
				rsList.add(allvo);
			}
		}	
	
		Map<String,List<NetflowWRData>>  rsMap = new HashMap<String, List<NetflowWRData>>();
		for (NetflowWRData vo : rsList) {
			
			String type = vo.getType().substring(0,vo.getType().indexOf("_"));
			
			List<NetflowWRData> tempList = new ArrayList<NetflowWRData>();
			if(rsMap.containsKey(type)){
				tempList = rsMap.get(type);
			}		
			tempList.add(vo);
			rsMap.put(type, tempList);
		}		
		return rsMap;
	}

	@Override
	public List<NetflowWRData> getNWRCate2Data(List<String> dateList,String platform,String business)
			throws Exception {

		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();

		for(String searchDate : dateList){
			//跨月检查  
			List<String> monthList = checkTwoMonth(searchDate);			
			
			//只有58				
			sql.delete(0, sql.length()+1);					
			sql.append(" select '" + searchDate + "', a.disp_cate2_name,");
			sql.append(" round(sum(a.vppv)/ count(distinct a.stat_date),0)  as'vppv', ");
			sql.append(" round(sum(a.uv)/ count(distinct a.stat_date),0)  as'uv',");
			sql.append(" round(sum(a.pv)/ count(distinct a.stat_date),0)  as'pv', ");
			sql.append(" round(sum(a.vppv)/ sum(a.uv),2)  as'vppv/uv' ");
			//app端没有跳出
			if(!"app".equals(platform)){
				
				sql.append(" ,round(sum(a.jump_out)/ sum(a.visit),4)  as'jumpout_rate' ");
			}
			
			sql.append(" from( ");
					
			sql.append(" select stat_date,disp_cate2_name,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ," +
						"sum(case when page_type='all' then pv else 0  end) pv ");			
		
			//app端没有跳出率
			if(!"app".equals(platform)){
				
				sql.append(" ,sum(case when page_type='all' then jump_out else 0  end) jump_out,sum(case when page_type='all' then visit else 0  end) visit  ");
			}
			
			if("app".equals(platform)){
						
				sql.append(" from t_netflow_"+platform+"_pv_"  + monthList.get(0).split(";")[0]);
			}
			else{
				sql.append(" from t_netflow_"+platform+"_source_cate_pv_" + monthList.get(0).split(";")[0]);
			}	
					
			sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
			sql.append(" and stat_date <= '"+searchDate+ "' ");					
			sql.append(" and business_type = '"+business+"' ");
			if("app".equals(platform)){
				sql.append(" and  disp_city1_name='all' ");
			}
			else{
				sql.append(" and source1='all'");
			}
				
			if(business.equals("zhaopin"))
			{
				sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') and disp_cate2_name in  ('普工/技工','销售','人事/行政/后勤','餐饮','超市/百货/零售','司机/交通服务','物流/仓储'," +
						"'家政保洁/安保','客服','财务/审计/统计','教育培训','传单派发','促销/导购','学生兼职','计算机/互联网/通信','服务员','美容/美发','淘宝职位','钟点工','建筑') ");
			}else if(business.equals("huangye"))
			{
				sql.append(" and disp_cate1_name in ('商务服务','家政服务','装修建材','汽车服务','教育培训','招商加盟','旅游酒店','婚庆摄影','批发采购') and disp_cate2_name in ('工商注册','搬家'," +
						"'家装服务','租车','保姆/月嫂','投资担保','工装服务','二手回收','保洁清洗','职业技能培训','餐饮加盟','建材','周边游','艺术培训','家电维修','婚庆','快递','驾校','食品','房屋维修/防水') ");
			}else if(business.equals("ershouchuangxin"))
			{
				sql.append(" and disp_cate1_name in ('宠物','二手交易','回收')");							
			}
			
			if("pc".equals(platform) || "app".equals(platform)){
				sql.append(" and  disp_cate3_name='all' ");
			}
		
			sql.append(" group by stat_date,disp_cate2_name ");	
					
			//跨月处理
			if(monthList.size() > 1){
						
				sql.append(" UNION all  ");						
				sql.append(" select stat_date,disp_cate2_name,sum(case when page_type='detail' then pv else 0  end) vppv,sum(case when page_type='all' then uv else 0  end) uv ," +
					"sum(case when page_type='all' then pv else 0  end) pv ");			
	
				if(!"app".equals(platform)){
					
					sql.append(" ,sum(case when page_type='all' then jump_out else 0  end) jump_out,sum(case when page_type='all' then visit else 0  end) visit  ");
				}
				
				if("app".equals(platform)){
							
					sql.append(" from t_netflow_"+platform+"_pv_"  + monthList.get(1).split(";")[0]);
				}
				else{
					sql.append(" from t_netflow_"+platform+"_source_cate_pv_" + monthList.get(1).split(";")[0]);
				}	
						
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");							
				sql.append(" and business_type = '"+business+"' ");
				if("app".equals(platform)){
					sql.append(" and  disp_city1_name='all' ");
				}
				else{
					sql.append(" and source1='all'");
				}
					
				if(business.equals("zhaopin"))
				{
					sql.append(" and disp_cate1_name in ('全职招聘','兼职招聘') and disp_cate2_name in  ('普工/技工','销售','人事/行政/后勤','餐饮','超市/百货/零售','司机/交通服务','物流/仓储'," +
							"'家政保洁/安保','客服','财务/审计/统计','教育培训','传单派发','促销/导购','学生兼职','计算机/互联网/通信','服务员','美容/美发','淘宝职位','钟点工','建筑') ");
				}else if(business.equals("huangye"))
				{
					sql.append(" and disp_cate1_name in ('商务服务','家政服务','装修建材','汽车服务','教育培训','招商加盟','旅游酒店','婚庆摄影','批发采购') and disp_cate2_name in ('工商注册','搬家'," +
							"'家装服务','租车','保姆/月嫂','投资担保','工装服务','二手回收','保洁清洗','职业技能培训','餐饮加盟','建材','周边游','艺术培训','家电维修','婚庆','快递','驾校','食品','房屋维修/防水') ");
				}else if(business.equals("ershouchuangxin"))
				{
					sql.append(" and disp_cate1_name in ('宠物','二手交易','回收')");							
				}	
				
				if("pc".equals(platform) || "app".equals(platform)){
					sql.append(" and  disp_cate3_name='all' ");
				}
				
				sql.append(" group by stat_date,disp_cate2_name ");				
			}
					
			sql.append(" )a ");	
			sql.append(" group by disp_cate2_name ");	
					
			sql.append(" :"+ DBType.db_94_data_app.getValue());
													
			List<String> cols1 = new ArrayList<String>();
			cols1.add("statDate");
			cols1.add("type");
			cols1.add("vppv");
			cols1.add("uv");
			cols1.add("pv");
			cols1.add("vppv_uv");
						
			if(!"app".equals(platform)){
				
				cols1.add("jumpout_rate");
			}

			sqlMap.put(sql.toString(), cols1);
				
			//只pc端有转化率			
			if(platform.equals("pc"))
			{
				sql.delete(0, sql.length()+1);					
				sql.append(" select '" + searchDate + "', a.disp_cate2_name_from,");
				sql.append(" round(sum(a.detailpv)/ sum(a.listpv),2)  as'range_rate' ");
				sql.append(" from( ");
						
				sql.append(" select stat_date,disp_cate2_name_from,sum(case when page_type_to='detail' then pv else 0  end) detailpv,sum(case when page_type_from='list' then pv else 0  end) listpv ");			
				sql.append(" from t_netflow_pagetype_lossrate_pc_cate_" + monthList.get(0).split(";")[0]);
												
				sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
				sql.append(" and stat_date <= '"+searchDate+ "' ");							
				sql.append(" and business_type_from = '"+business+"' ");
							
				if(business.equals("zhaopin"))
				{
					sql.append(" and disp_cate1_name_from in ('全职招聘','兼职招聘') and disp_cate2_name_from in  ('普工/技工','销售','人事/行政/后勤','餐饮','超市/百货/零售','司机/交通服务','物流/仓储'," +
							"'家政保洁/安保','客服','财务/审计/统计','教育培训','传单派发','促销/导购','学生兼职','计算机/互联网/通信','服务员','美容/美发','淘宝职位','钟点工','建筑') ");
				}else if(business.equals("huangye"))
				{
					sql.append(" and disp_cate1_name_from in ('商务服务','家政服务','装修建材','汽车服务','教育培训','招商加盟','旅游酒店','婚庆摄影','批发采购') and disp_cate2_name_from in ('工商注册','搬家'," +
							"'家装服务','租车','保姆/月嫂','投资担保','工装服务','二手回收','保洁清洗','职业技能培训','餐饮加盟','建材','周边游','艺术培训','家电维修','婚庆','快递','驾校','食品','房屋维修/防水') ");
				}else if(business.equals("ershouchuangxin"))
				{
					sql.append(" and disp_cate1_name_from in ('宠物','二手交易','回收')");							
				}	
				sql.append(" group by stat_date,disp_cate2_name_from ");	
		
				//跨月处理
				if(monthList.size() > 1){
						
					sql.append(" UNION all  ");							
					sql.append(" select stat_date,disp_cate2_name_from,sum(case when page_type_to='detail' then pv else 0  end) detailpv,sum(case when page_type_from='list' then pv else 0  end) listpv ");			
					
					sql.append(" from t_netflow_pagetype_lossrate_pc_cate_" + monthList.get(1).split(";")[0]);													
					sql.append(" where stat_date >= date_sub('"+searchDate+"',interval 6 day)  ");
					sql.append(" and stat_date <= '"+searchDate+ "' ");							
					sql.append(" and business_type_from = '"+business+"' ");
								
					if(business.equals("zhaopin"))
					{
						sql.append(" and disp_cate1_name_from in ('全职招聘','兼职招聘') and disp_cate2_name_from in  ('普工/技工','销售','人事/行政/后勤','餐饮','超市/百货/零售','司机/交通服务','物流/仓储'," +
								"'家政保洁/安保','客服','财务/审计/统计','教育培训','传单派发','促销/导购','学生兼职','计算机/互联网/通信','服务员','美容/美发','淘宝职位','钟点工','建筑') ");
					}else if(business.equals("huangye"))
					{
						sql.append(" and disp_cate1_name_from in ('商务服务','家政服务','装修建材','汽车服务','教育培训','招商加盟','旅游酒店','婚庆摄影','批发采购') and disp_cate2_name_from in ('工商注册','搬家'," +
								"'家装服务','租车','保姆/月嫂','投资担保','工装服务','二手回收','保洁清洗','职业技能培训','餐饮加盟','建材','周边游','艺术培训','家电维修','婚庆','快递','驾校','食品','房屋维修/防水') ");
					}else if(business.equals("ershouchuangxin"))
					{
						sql.append(" and disp_cate1_name_from in ('宠物','二手交易','回收')");							
					}	
					sql.append(" group by stat_date,disp_cate2_name_from ");				
				}
					
				sql.append(" )a ");	
				sql.append(" group by disp_cate2_name_from ");	
				sql.append(" :"+ DBType.db_94_data_app.getValue());
													
				List<String> cols2 = new ArrayList<String>();
				cols2.add("statDate");
				cols2.add("type");
				cols2.add("range_rate");

				sqlMap.put(sql.toString(), cols2);	
			}						
		}	
		
		List<String> gbList = new ArrayList<String>();
		gbList.add("statDate");
		gbList.add("type");
		
		List<NetflowWRData> rsList = comDeal.AssembledDataBySqlMap(sqlMap, NetflowWRData.class, gbList);
		return rsList;
	}

	@Override
	public List<NetflowWRData> getNWRADDList(List<String> dateList,List<NetflowWRData> PCData, List<NetflowWRData> MData,List<NetflowWRData> APPData)
			throws Exception {

		List<NetflowWRData> rsList=new ArrayList<NetflowWRData>();

		//以pc为基准 来匹配m和app的
		for (NetflowWRData pcvo : PCData)
		{
			NetflowWRData allvo = new NetflowWRData();
			allvo.setStatDate(pcvo.getStatDate());
			allvo.setType(pcvo.getType());
				
			allvo.setVppv(pcvo.getVppv()+allvo.getVppv());
			allvo.setUv(pcvo.getUv()+allvo.getUv());		
			allvo.setPv(pcvo.getPv()+allvo.getPv());	
						
			for (NetflowWRData mvo : MData)
			{
				if(mvo.getType().equals(pcvo.getType()) && mvo.getStatDate().equals(pcvo.getStatDate()))
				{
					allvo.setVppv(mvo.getVppv()+allvo.getVppv());
					allvo.setUv(mvo.getUv()+allvo.getUv());		
					allvo.setPv(mvo.getPv()+allvo.getPv());	
					break;
				}			
			}
				
			for (NetflowWRData appvo : APPData)
			{
				if(appvo.getType().equals(pcvo.getType()) && appvo.getStatDate().equals(pcvo.getStatDate()))
				{
					allvo.setVppv(appvo.getVppv()+allvo.getVppv());
					allvo.setUv(appvo.getUv()+allvo.getUv());		
					allvo.setPv(appvo.getPv()+allvo.getPv());	
					break;
				}			
			}

			allvo.setVppv_uv((double)allvo.getVppv()/allvo.getUv());				
			rsList.add(allvo);
		}		
		
		return rsList;
	}

}
